PythonでODBC接続を行えるpyodbcをインストールする
はじめに
データアナリティクス事業本部のkobayashiです。
前回AmazonLinux，macOS環境でSQL Serverからデータを取得するためにsqlcmd、bcpをインストールしコマンドラインからデータをエクスポートする方法を紹介しました。
その後工程の加工処理をPythonで行っていましたがSQL Serverからデータを取得する処理もPythonで行いすべての工程をPythonで実行することにしSQL Serverへの接続にpyodbcを利用しました。またpyodbcはSQL Server以外のデータソースへもODBCドライバを用意し設定することで接続できるので合わせてそちらも設定しましたのでまとめます。
前回の記事
環境
- macOS Mojave
- AmazonLinux2
- Python 3.6.5
- pyodbc 4.0.27
pydobc
pyodbcはODBCを利用して様々なデータベースにPythonから接続して操作を行えます。
pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience.
インストール方法
pipがインストールしてあればインストールは以下のコマンドだけでpyodbcをインストールできます。
$ pip install pyodbc
ドライバファイルの設定
macOSとAmazonLinuxでpyodbcが接続に使うドライバの設定ファイルの場所が違います。
macOS
pyodbcが利用するドライバの設定ファイルは以下の場所にあります。
$ cat /usr/local/etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/usr/local/lib/libmsodbcsql.17.dylib UsageCount=1
SQL Serverのコマンドラインツールをインストールすると自動的にこのファイルに設定が書き込まれているのでSQL Serverに接続する場合は特にこのファイルの記述を変更する必要はありません。
PostgreSQLの設定
PostgreSQLの設定を行います。 まずはドライバをインストールします。
$ brew install psqlodbc
インストールが完了したら設定ファイルに追記します。
$ cat /usr/local/etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/usr/local/lib/libmsodbcsql.17.dylib UsageCount=1 [PostgreSQL] Driver=/usr/local/lib/psqlodbcw.so
Redshiftの設定
次にRedshiftの設定を行います。 ODBCドライバのインストールはAWSの公式ページに従って行えば特に問題なく行えます。
公式ページの手順に従ってインストールを終えると
/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib
にドライバファイルができるので設定ファイルに追記します。
$ cat /usr/local/etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/usr/local/lib/libmsodbcsql.17.dylib UsageCount=1 [PostgreSQL] Driver=/usr/local/lib/psqlodbcw.so [Redshift] Driver=/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib
AmazonLinux
pyodbcが利用するドライバの設定ファイルは以下の場所にあります。
$ cat /etc/odbcinst.ini # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbcw.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1
すでにPostgreSQLとMySQLの設定はされているのでここにSQL ServerとRedshiftの設定を追記します。
SQL Serverの設定
前回でインストールは終わっているのでドライバファイルもインストールされていますので設定ファイルに追記します。
$ cat /etc/odbcinst.ini # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbcw.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 [ODBC Driver 17 for SQL Server] Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1 UsageCount=1
Redshiftの設定
次にRedshiftの設定を行います。 ODBCドライバのインストールはAWSの公式ページに従って行えば特に問題なく行えます。 RHELの64 ビットをインストールします。
公式ページの手順に従ってインストールを終えると
/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
にドライバファイルができるので設定ファイルに追記します。
$ cat /etc/odbcinst.ini # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbcw.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 [ODBC Driver 17 for SQL Server] Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1 UsageCount=1 [Redshift] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
以上で設定は終わりです。
pyodbcでデータソースに接続する
ここまでの設定を行えばpydobcを使って各データソースに接続できるようになります。
import pyodbc
cnn_sqlserfer = pyodbc.connect('DRIVER=ODBC Driver 17 for SQL Server;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')
cnn_postgres = pyodbc.connect('DRIVER=PostgreSQL;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')
cnn_redshift = pyodbc.connect('DRIVER=Redshift;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')
あとはコネクションを張ってカーソルを作成してといった方法で操作が可能です。詳しい使い方は公式ドキュメントが非常にわかりやすのでそちらをご確認ください。
まとめ
Linux、macOSでSQL Serverを含めた様々なデータソースに接続でき操作を行えるpyodbcの設定をまとめました。 これでデータソースに接続してデータを取得して加工するといった処理がPythonで行えるようになります。Pythonを利用すればPandasやPySparkを使い大規模なデータ処理も簡単に行えるようになり非常に効率が良いです。
最後まで読んで頂いてありがとうございました。
